package com.webapp.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class HandleReport {
    public String getHandleReport(String path){
        
        File dir = new File(path);
        File[] files = dir.listFiles(); // 该文件目录下文件全部放入数组
        List<Map<String, Object>> list =new ArrayList<Map<String, Object>>();
        if (files != null) {
            for (int i = 0; i < files.length; i++) {
                String fileName = files[i].getName();
                Map<String, Object> map = new HashMap<String, Object>();
                map.put("data", getData(path+"\\"+fileName));
                map.put("sheet", fileName);
                list.add(map);
            }

        }
        return biuldExcel(list);

    }
    
    static Map<String , HashMap<String, String>> getData(String fileName){
        File file = new File(fileName);
        Map<String ,HashMap<String, String> > map = new HashMap<String ,HashMap<String, String>>();
        
        if (!file.exists())
            System.out.println("文件不存在");
        try {
            //1.读取Excel的对象
            FileInputStream is = new FileInputStream(file);
            //POIFSFileSystem fs = new POIFSFileSystem(is);
            //2.Excel工作薄对象
            XSSFWorkbook hssfWorkbook = new XSSFWorkbook(is);
            //3.Excel工作表对象
            XSSFSheet hssfSheet = hssfWorkbook.getSheetAt(0);
            
            //总行数
            int rowLength = hssfSheet.getLastRowNum()+1;
            //4.得到Excel工作表的行
            XSSFRow hssfRow = hssfSheet.getRow(6);
            //总列数
            int colLength = hssfRow.getLastCellNum();
           
            System.out.println(rowLength);
            System.out.println(colLength);
            System.out.println();
            System.out.println();
            
            //获取列名
            List<String> list = new ArrayList<String>();
            for(int i=0;i<colLength;i++){
                String string = hssfRow.getCell(i).getStringCellValue().trim();
                if(string.equals("Jan")){
                    string = "January";
                }
                if(string.equals("Feb")){
                    string = "February";
                }
                if(string.equals("Mar")){
                    string = "March";
                }
                if(string.equals("Apr")){
                    string = "April";
                }
                if(string.equals("May")){
                    string = "May";
                }
                if(string.equals("Jun")){
                    string = "June";
                }
                if(string.equals("Jul")){
                    string = "July";
                }
                if(string.equals("Aug")){
                    string = "August";
                }
                if(string.equals("Sep")){
                    string = "September";
                }
                if(string.equals("Oct")){
                    string = "October";
                }
                if(string.equals("Nov")){
                    string = "November";
                }
                if(string.equals("Dec")){
                    string = "December";
                }
            
                list.add(string);
            }
            
            for (int i = 7; i < rowLength; i++) {
                //获取Excel工作表的行
                XSSFRow hssfRow1 = hssfSheet.getRow(i);
                HashMap<String, String> hashMap = new HashMap<String, String>();
                Double double1=0.0;
                for (int j = 0; j < colLength; j++) {
                    //获取指定单元格
                    XSSFCell hssfCell = hssfRow1.getCell(j);
                    if(j>=8){
                        double1 += hssfCell.getNumericCellValue();
                    }
                    if(hssfCell != null){
                        hssfCell.setCellType(CellType.STRING);
                    }
                   hashMap.put(list.get(j), hssfCell.getStringCellValue().trim());
                }
                hashMap.put("Total", new BigDecimal(double1.toString()).toString());
                if(hashMap.get("Display_Head") == null || hashMap.get("Display_Head").equals("")){
                    String dis = "";
                    
                    for(int x=i-1;;x--){
                        dis = hssfSheet.getRow(x).getCell(0).getStringCellValue().trim();
                        if(dis == null || dis.equals("")){
                            continue;
                        }else{
                            break;
                        }
                    }
                    if(dis.equals("Display_Head")){
                        dis = "";
                    }
                    hashMap.put("Display_Head", dis);
                }
                map.put(hssfRow1.getCell(3).getStringCellValue().trim(), hashMap);
               
            }
            HashMap<String, String> row = new HashMap<String, String>();
            row.put("rowLength", String.valueOf(rowLength));
            map.put("rowLength", row);
            is.close();
            
        } catch (IOException e) {
            e.printStackTrace();
        }   
        
        return map;
    }
    
    static String biuldExcel(List<Map<String, Object>> list){
        
        SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");//设置日期格式
        String date = df.format(new Date());// new Date()为获取当前系统时间，也可使用当前时间戳
        String sFile = "C:\\Users\\gyj\\Desktop\\PWCcognos报表\\测试报表\\"+date+".xls";
        
        try{
            //创建excel工作簿
            HSSFWorkbook hssfWorkbook1=new HSSFWorkbook();
            int x = 1;
            for (Map<String, Object> map : list) {
            if(map != null){
                //创建工作表sheet
                
//                    System.out.println(x);
//                    int endIndex = ((String)map.get("sheet")).indexOf(".");
//                    String string = ((String)map.get("sheet")).substring(0, endIndex);
//                    System.out.println(string);
                HSSFSheet sheet=hssfWorkbook1.createSheet(""+x);
                if(map.get("data") == null){
                    x++;
                    continue;
                }
                String rowLength = ((Map<String , HashMap<String, String>>)map.get("data")).get("rowLength").get("rowLength");
                
                String [] strings = {"","","July","August","September","October","November","December","January","February","March","April","May","June","Total"};
                for(int i=0;i<151;i++){
                    if(i==0){
                        
                        HSSFRow row=sheet.createRow(i);
                        for(int j=0;j<=14;j++){
                            //style.setFillBackgroundColor(YELLOW.index);
                            HSSFCell cell = row.createCell(j);
                            HSSFCellStyle style = hssfWorkbook1.createCellStyle();
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    
                            style.setFillForegroundColor(IndexedColors.YELLOW.index);
                            style.setAlignment(HorizontalAlignment.CENTER);
                            style.setBorderBottom(BorderStyle.THIN); // 下边框
                            style.setBorderTop(BorderStyle.THIN);// 上边框
                            if(j>1){
                                style.setBorderLeft(BorderStyle.THIN);// 左边框
                                style.setBorderRight(BorderStyle.THIN);// 右边框
                            }
                            cell.setCellStyle(style);
                            cell.setCellValue(strings[j]);
                        }
                        continue;
                    }
                    HSSFRow row=sheet.createRow(i);
                    String index = i+"";
        //            System.out.println(index);
                    Map<String, String> hashMap1 = ((Map<String , HashMap<String, String>>)map.get("data")).get(index) != null? ((Map<String , HashMap<String, String>>)map.get("data")).get(index) : null;
                    if(hashMap1 == null){
                        continue;
                    }
                    String title = hashMap1.get("Title");
                    String blank_space = hashMap1.get("Blank_Space") ;
                    String bold = hashMap1.get("Bold");
                    String color_fill = hashMap1.get("Color_Fill");
                    String debit = hashMap1.get("Debit");
                    String display_head = hashMap1.get("Display_Head");
                    int y = 0;
                    if(blank_space.equals("S0")){
                    	y = 1;
                    }
                    if(blank_space.equals("S2")){
                    	y = 2;
                    }
                    if(blank_space.equals("S4")){
                    	y = 3;
                    }
                    switch (y) {
                    case 0:
                    	break;
                    case 1:
                        break;
                        
                    case 2:
                        display_head = "    "+display_head;
                        break;
                    case 3:
                        display_head = "        "+display_head;
                        break;
                    default:
                        break;
                    }
                    
                    for(int j=0;j<=14;j++){
                        HSSFCell cell = row.createCell(j);
                        
                        if(j==0){
                            HSSFCellStyle style = hssfWorkbook1.createCellStyle();
                            HSSFFont font = hssfWorkbook1.createFont();
                            cell.setCellValue(display_head);
                            if(bold.equals("Y")){
                                font.setBold(true);
                                style.setFont(font);
                            }
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    
                            style.setFillForegroundColor(IndexedColors.YELLOW.index);
                            cell.setCellStyle(style);
                            sheet.setColumnWidth(j, 33*256+184);
                            
                            continue;
                        }
                        if(j==1){
                            HSSFCellStyle style = hssfWorkbook1.createCellStyle();
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    
                            style.setFillForegroundColor(IndexedColors.YELLOW.index);
                            cell.setCellStyle(style);
                            sheet.setColumnWidth(j, 22*256+184);
                            continue;
                        }
                        
                        HSSFCellStyle style = hssfWorkbook1.createCellStyle();
                        HSSFFont font = hssfWorkbook1.createFont();
                        
                        if(color_fill.equals("Y")){
                            font.setColor(IndexedColors.BLUE.index);
                            style.setFont(font);
                            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);    
                            style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
                        }
                        String num = hashMap1.get(strings[j]);
                        Double double1=null;
                        if(num != null && !num.equals("")){
                            double1 = Double.parseDouble(num);
                            int z = 0;
                            if(debit.equals("A")){
                            	z = 1;
                            }
                            if(debit.equals("B")){
                            	z = 2;
                            }
                            if(debit.equals("C")){
                            	z = 3;
                            }
                            if(debit.equals("D")){
                            	z = 4;
                            }
                            switch (z) {
                            case 0:
                            	break;
                            	
                            case 1://零位小数，正数带括弧，负数显示正数
                                style.setDataFormat(hssfWorkbook1.createDataFormat().getFormat("(#,##0);#,##0_);-_)"));//自定义显示方式
                                break;
                                
                            case 2://零位小数，正数显示正数，负数带括弧
                                style.setDataFormat(hssfWorkbook1.createDataFormat().getFormat("#,##0_);(#,##0);-_)"));
                                break;
                                
                            case 3://一位小数，正数带括弧，负数显示正数
                                style.setDataFormat(hssfWorkbook1.createDataFormat().getFormat("(#,##0.0);#,##0.0_);-_)"));
                                break;
                                
                            case 4://一位小数，正数显示正数，负数带括弧
                                style.setDataFormat(hssfWorkbook1.createDataFormat().getFormat("#,##0.0_);(#,##0.0);-_)"));
                                break;
                                
                            default:
                                break;
                            }
        
                        } 
                        sheet.setColumnWidth(j, 22*256+184);
                        if(j==14){
                            sheet.setColumnWidth(j, 25*256+184);
                        }
                        cell.setCellStyle(style);
                        if(double1 == null){
                            cell.setCellValue("");
                        }else{
                            cell.setCellValue(double1);
                        }
                       
                    }
                    
                }
                x++;
            }
            //System.out.println("---");
            }
            File file1 = new File(sFile);
            FileOutputStream os = new FileOutputStream(file1);
            hssfWorkbook1.write(os);
            
            os.close();
          
        } catch (IOException e) {
            e.printStackTrace();
        }
        return sFile;
    }
}
